﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using DynORM.Interfaces;
using DynORM.Mappers;
using DynORM.Enums;
using DynORM;
using DynORM.ValueObjects;
using DynORM.ReferenceObjects;

namespace UnitTests.DAL
{
    [TestClass]
    public class MSSQLDALManyToManyUnitTests
    {
        static string dbName = "TestDB";
        static string pooling = ";Pooling=false";//otherwise it won't let drop db (db will be in use with connections in pool)
        static string connectionStringToDatabase = "Data Source=.;Initial Catalog=" + dbName + ";Integrated Security=True" + pooling;
        static string connectionStringToServer = "Data Source=.;Integrated Security=True" + pooling;
        static DBMSTypes dbmsTypes = DBMSTypes.MSSQL;

        static string tableName1 = "TestTable1";
        static string tableName2 = "TestTable2";
        static string linkTableName = String.Format("{0}_{1}", tableName1, tableName2);

        [TestInitialize]
        public void TestInit()
        {
            DatabaseMapper.CreateDatabase(connectionStringToServer, DBMSTypes.MSSQL, new Database(dbName));
        }

        [TestCleanup]
        public void TestClean()
        {
            DatabaseMapper.DropDatabase(connectionStringToServer, DBMSTypes.MSSQL, dbName);
        }

        void CreateNewRowDescriptors()
        {
            Dictionary<int, IFieldDescriptor> pkFieldDescs = new Dictionary<int, IFieldDescriptor>();
            pkFieldDescs.Add(0, new FieldDescriptor(typeof(Guid), "RowID"));
            PrimaryKeyDescriptor pkd = new PrimaryKeyDescriptor(tableName1, pkFieldDescs, tableName1 + "_PK");
            List<FieldDescriptor> fieldDescs = new List<FieldDescriptor>();
            RowDescriptor rd = new RowDescriptor(tableName1, fieldDescs, new List<IRelationDescriptor>() { pkd });
            RowDescriptorMapper.Store(connectionStringToDatabase, dbmsTypes, rd);

            
            pkFieldDescs = new Dictionary<int, IFieldDescriptor>();
            pkFieldDescs.Add(0, new FieldDescriptor(typeof(Guid), "RowID"));
            pkd = new PrimaryKeyDescriptor(tableName2, pkFieldDescs, tableName2 + "_PK");
            fieldDescs = new List<FieldDescriptor>();
            rd = new RowDescriptor(tableName2, fieldDescs, new List<IRelationDescriptor>() { pkd });
            RowDescriptorMapper.Store(connectionStringToDatabase, dbmsTypes, rd);

            pkFieldDescs = new Dictionary<int, IFieldDescriptor>();
            pkFieldDescs.Add(0, new FieldDescriptor(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName1, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs)));
            pkFieldDescs.Add(1, new FieldDescriptor(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName2, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs)));
            pkd = new PrimaryKeyDescriptor(linkTableName, pkFieldDescs, linkTableName + "_PK");
            
            Dictionary<int, IFieldDescriptor> fkFieldDescs1 = new Dictionary<int, IFieldDescriptor>();
            fkFieldDescs1.Add(0, new FieldDescriptor(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName1, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs)));
            Dictionary<int, IFieldDescriptor> relfkFieldDescs1 = new Dictionary<int, IFieldDescriptor>();
            relfkFieldDescs1.Add(0, new FieldDescriptor(typeof(Guid), "RowID"));
            ForeignKeyDescriptor fkd1 = new ForeignKeyDescriptor(linkTableName, fkFieldDescs1, tableName1, relfkFieldDescs1, String.Format("{0}_{1}_FK", linkTableName, tableName1));
            
            Dictionary<int, IFieldDescriptor> fkFieldDescs2 = new Dictionary<int, IFieldDescriptor>();
            fkFieldDescs2.Add(0, new FieldDescriptor(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName2, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs)));
            Dictionary<int, IFieldDescriptor> relfkFieldDescs2 = new Dictionary<int, IFieldDescriptor>();
            relfkFieldDescs2.Add(0, new FieldDescriptor(typeof(Guid), "RowID"));
            ForeignKeyDescriptor fkd2 = new ForeignKeyDescriptor(linkTableName, fkFieldDescs2, tableName2, relfkFieldDescs2, String.Format("{0}_{1}_FK", linkTableName, tableName2));

            rd = new RowDescriptor(linkTableName, fieldDescs, new List<IRelationDescriptor>() { pkd, fkd1, fkd2 });
            RowDescriptorMapper.Store(connectionStringToDatabase, dbmsTypes, rd);
        }

        void DropRowDescriptors()
        {
            RowDescriptorMapper.Drop(connectionStringToDatabase, dbmsTypes, linkTableName);
            RowDescriptorMapper.Drop(connectionStringToDatabase, dbmsTypes, tableName1);
            RowDescriptorMapper.Drop(connectionStringToDatabase, dbmsTypes, tableName2);
        }

        [TestMethod]
        public void CreateManyToManyRowDescriptorTest()
        {
            CreateNewRowDescriptors();
            IRowDescriptor rd1 = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, tableName1);
            IRowDescriptor rd2 = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, tableName2);
            IRowDescriptor linkrd = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, linkTableName);
            Assert.AreEqual(1, rd1.RelationDescriptors.Where<IRelationDescriptor>(r => r.RelationType == RelationType.PrimaryKey).Count<IRelationDescriptor>());
            Assert.AreEqual(1, rd2.RelationDescriptors.Where<IRelationDescriptor>(r => r.RelationType == RelationType.PrimaryKey).Count<IRelationDescriptor>());
            Assert.AreEqual(1, linkrd.RelationDescriptors.Where<IRelationDescriptor>(r => r.RelationType == RelationType.PrimaryKey).Count<IRelationDescriptor>());
            Assert.AreEqual(2, linkrd.RelationDescriptors.Where<IRelationDescriptor>(r => r.RelationType == RelationType.ForeignKey).Count<IRelationDescriptor>());
            DropRowDescriptors();
        }

        void AddRows(Guid id1, Guid id2)
        {
            IRowDescriptor rd1 = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, tableName1);
            Row row1 = new Row(rd1, id1);
            RowMapper.RowWrite(connectionStringToDatabase, dbmsTypes, row1);

            IRowDescriptor rd2 = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, tableName2);
            Row row2 = new Row(rd2, id2);
            RowMapper.RowWrite(connectionStringToDatabase, dbmsTypes, row2);

            IRowDescriptor linkrd = RowDescriptorMapper.Retrieve(connectionStringToDatabase, dbmsTypes, linkTableName);
            IPrimaryKey linkpk = new PrimaryKey(linkrd.PrimaryKeyDescriptor, id1, id2);
            IForeignKeyDescriptor linkfkd1 = linkrd.ForeignKeyDescriptors.First<IForeignKeyDescriptor>(fkd => String.Compare(fkd.RelatedTableName, tableName1, StringComparison.OrdinalIgnoreCase) == 0);
            IForeignKey linkfk1 = new ForeignKey(linkfkd1, id1, id1);
            IForeignKeyDescriptor linkfkd2 = linkrd.ForeignKeyDescriptors.First<IForeignKeyDescriptor>(fkd => String.Compare(fkd.RelatedTableName, tableName2, StringComparison.OrdinalIgnoreCase) == 0);
            IForeignKey linkfk2 = new ForeignKey(linkfkd2, id2, id2);
            Row linkrow = new Row(linkpk, new List<IField>(), new List<IForeignKey>() { linkfk1, linkfk2 });

            RowMapper.RowWrite(connectionStringToDatabase, dbmsTypes, linkrow);
        }

        [TestMethod]
        public void AddManyToManyRowTest()
        {
            CreateNewRowDescriptors();
            Guid id1 = Guid.NewGuid();
            Guid id2 = Guid.NewGuid();
            AddRows(id1, id2);

            Dictionary<int, IField> fs1 = new Dictionary<int, IField>();
            fs1.Add(0, new Field(typeof(Guid), "RowID", id1));
            IPrimaryKey pk1 = new PrimaryKey(tableName1, fs1, tableName1 + "_PK");
            IRow row1 = RowMapper.RowRead(connectionStringToDatabase, DBMSTypes.MSSQL, pk1);
            Assert.IsNotNull(row1);

            Dictionary<int, IField> fs2 = new Dictionary<int, IField>();
            fs2.Add(0, new Field(typeof(Guid), "RowID", id2));
            IPrimaryKey pk2 = new PrimaryKey(tableName2, fs2, tableName2 + "_PK");
            IRow row2 = RowMapper.RowRead(connectionStringToDatabase, DBMSTypes.MSSQL, pk2);
            Assert.IsNotNull(row2);

            Dictionary<int, IField> linkfs = new Dictionary<int, IField>();
            linkfs.Add(0, new Field(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName1, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs), id1));
            linkfs.Add(1, new Field(typeof(Guid), String.Format("{0}_{1}_FK_{2}", linkTableName, tableName2, ForeignKeyDescriptor.PredefinedFieldNameForForeignIDs), id2));
            IPrimaryKey linkpk = new PrimaryKey(linkTableName, linkfs, linkTableName + "_PK");
            IRow linkrow = RowMapper.RowRead(connectionStringToDatabase, DBMSTypes.MSSQL, linkpk);
            Assert.IsNotNull(linkrow);

            DropRowDescriptors();
        }



    }
}
